package com.mouse.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mouse.entity.Disease;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mouse.entity.GeneCategory;
import com.mouse.entity.GeneComposition;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author godson
 * @since 2022-08-07
 */
public interface DiseaseMapper extends BaseMapper<Disease> {

    @Select("Select dis.* from gene_homology gho LEFT JOIN disease dis ON dis.sid=gho.sid WHERE gho.gid=#{gid}")
    List<Disease> getDiseaseByGid( @Param("gid") Long gid);

    @Select("SELECT s.* FROM (SELECT gc.* FROM gene_composition gcom LEFT JOIN genecategory_genecomposition gcgcom ON gcom.gcid=gcgcom.gcid LEFT JOIN gene_category gc ON gcgcom.gid=gc.gid WHERE gcom.gcid=#{gcid}) gc_result\n" +
            "LEFT JOIN gene_homology gh ON gc_result.gid = gh.gid LEFT JOIN disease s ON gh.sid= s.sid")
    List<Disease> getDiseaseByGcid(@Param("gcid") Long gcid);

    /***
     * 3.2.2.3
     * 忽略了NULL值
     * @param did
     * @return
     */
    @Select("SELECT * FROM" +
            "(SELECT d.* FROM (SELECT gcda.* FROM  double_alleles da LEFT JOIN gene_composition_double_alleles gcda ON da.did=gcda.did WHERE da.did=1) res \n" +
            "LEFT JOIN gene_composition gcom on res.gcid=gcom.gcid\n" +
            "LEFT JOIN genecategory_genecomposition ggcom ON gcom.gcid=ggcom.gcid LEFT JOIN gene_category gc ON gc.gid=ggcom.gid\n" +
            "LEFT JOIN gene_homology gh ON gh.gid=gc.gid LEFT JOIN disease d ON d.sid=gh.sid)" +
            " r WHERE r.sid IS NOT NULL")
    List<Disease> getDiseaseByDid(@Param("did") Long did);

    @Select("SELECT a.* FROM (SELECT DISTINCT d.* FROM gene_allele ga LEFT JOIN double_alleles_gene_allele daga ON ga.aid=daga.aid LEFT JOIN double_alleles da ON daga.did= da.did \n" +
            "LEFT JOIN gene_composition_double_alleles gcda ON gcda.did=da.did LEFT JOIN gene_composition gcom on gcda.gcid=gcom.gcid\n" +
            "LEFT JOIN genecategory_genecomposition gcgcom ON gcgcom.gcid=gcom.gcid LEFT JOIN gene_category gc ON gc.gid=gcgcom.gid\n" +
            "LEFT JOIN gene_homology gh ON gh.gid = gc.gid LEFT JOIN disease d ON d.sid=gh.sid\n" +
            "WHERE daga.aid=#{aid}) a WHERE a.sid IS NOT NULL")
    List<Disease> getDiseaseByAid(@Param("aid") Long aid);





}
